﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Application.Common;
using Application.Presentation;
using Interface.Security;
using Interface.FormBuilder;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Threading;
using System.Xml;

namespace IQCare.FormBuilder
{
    [Serializable]
    public partial class frmImportExportData : Form
    {
        DataTable theDT;
        DataSet theTableDS = new DataSet();
        public frmImportExportData()
        {
            InitializeComponent();
            rdoImport.CheckedChanged += new EventHandler(rdo_changed);
            rdoExport.CheckedChanged += new EventHandler(rdo_changed);
        }
        DataSet dsCollectDataToSave;

        private void frmImportExportData_Load(object sender, EventArgs e)
        {
            clsCssStyle theStyle = new clsCssStyle();
            theStyle.setStyle(this);

            IUser UserManager = (IUser)ObjectFactory.CreateInstance("BusinessProcess.Security.BUser,BusinessProcess.Security");
            theDT = UserManager.GetFacilityList();
            BindFunctions theBind = new BindFunctions();
            theBind.Win_BindCombo(cmbImpLocation, theDT, "FacilityName", "FacilityId");
            theBind.Win_BindCombo(cmbExpLocation, theDT, "FacilityName", "FacilityId");
            rdoImport.Checked = true;
            groupBox2.Enabled = false;
            groupBox1.Enabled = true;

        }


        private void rdo_changed(object s, EventArgs e)
        {
            if (((RadioButton)s).Name.ToString() == "rdoImport")
            {
                groupBox2.Enabled = false;
                groupBox1.Enabled = true;

            }
            else
            {
                groupBox1.Enabled = false;
                groupBox2.Enabled = true;
            }

        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            FileDialog oDialog = new OpenFileDialog();
            oDialog.DefaultExt = "xml";
            oDialog.Filter = "Form (*.xml)|*.xml";
            if (oDialog.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = oDialog.FileName;
            }
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Hide();
        }

        private DataSet CopyTablesfromDataset(DataSet objDsFormDetails)
        {
            DataSet dsExtractDataToSave = new DataSet();
            if (dsExtractDataToSave.Tables.Count == 0)
            {
                ////mst_feature
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["mst_feature"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["mst_feature"]);
                dsExtractDataToSave.Tables[0].TableName = "Table";
                ////mst_section
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["mst_section"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["mst_section"]);
                dsExtractDataToSave.Tables[1].TableName = "Table1";

                ////lnk_forms
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["lnk_forms"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["lnk_forms"]);
                dsExtractDataToSave.Tables[2].TableName = "Table2";
                ////Select List Val only for custom fields
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["mst_customFormField"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["mst_customFormField"]);
                dsExtractDataToSave.Tables[3].TableName = "Table3";
                ////Business Rule only for custom fields
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["lnk_fieldsBusinessRule"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["lnk_fieldsBusinessRule"]);
                dsExtractDataToSave.Tables[4].TableName = "Table4";
                ////mst_module-tech area
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["mst_patientIdentifier"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["mst_patientIdentifier"]);
                dsExtractDataToSave.Tables[5].TableName = "Table5";
                ////module identifier
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["lnk_patientModuleIdentifier"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["lnk_patientModuleIdentifier"]);
                dsExtractDataToSave.Tables[6].TableName = "Table6";
                ////Conditional Fields
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["Vw_FieldConditionalField"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["Vw_FieldConditionalField"]);
                dsExtractDataToSave.Tables[7].TableName = "Table7";
                ////Select List Val only for Conditional custom fields
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["VW_MasterTableLinking"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["VW_MasterTableLinking"]);
                dsExtractDataToSave.Tables[8].TableName = "Table8";
                ////Business Rule only for Conditional custom fields
                dsExtractDataToSave.Tables.Add(objDsFormDetails.Tables["lnk_fieldsConditionalBusinessRule"].Copy());
                objDsFormDetails.Tables.Remove(objDsFormDetails.Tables["lnk_fieldsConditionalBusinessRule"]);
                dsExtractDataToSave.Tables[9].TableName = "Table9";

            }
            else
            {
                foreach (DataRow dr in objDsFormDetails.Tables["mst_feature"].Rows)
                {
                    dsExtractDataToSave.Tables["Table"].ImportRow(dr);

                }
                foreach (DataRow dr in objDsFormDetails.Tables["mst_section"].Rows)
                {
                    dsExtractDataToSave.Tables["Table1"].ImportRow(dr);
                }
                foreach (DataRow dr in objDsFormDetails.Tables["lnk_forms"].Rows)
                {
                    dsExtractDataToSave.Tables["Table2"].ImportRow(dr);
                }
                foreach (DataRow dr in objDsFormDetails.Tables["mst_customFormField"].Rows)
                {
                    dsExtractDataToSave.Tables["Table3"].ImportRow(dr);
                }
                foreach (DataRow dr in objDsFormDetails.Tables["lnk_fieldsBusinessRule"].Rows)
                {
                    dsExtractDataToSave.Tables["Table4"].ImportRow(dr);
                }
                foreach (DataRow dr in objDsFormDetails.Tables["mst_patientIdentifier"].Rows)
                {
                    dsExtractDataToSave.Tables["Table5"].ImportRow(dr);
                }
                foreach (DataRow dr in objDsFormDetails.Tables["lnk_patientModuleIdentifier"].Rows)
                {
                    dsExtractDataToSave.Tables["Table6"].ImportRow(dr);
                }
                foreach (DataRow dr in objDsFormDetails.Tables["Vw_FieldConditionalField"].Rows)
                {
                    dsExtractDataToSave.Tables["Table7"].ImportRow(dr);
                }
                foreach (DataRow dr in objDsFormDetails.Tables["VW_MasterTableLinking"].Rows)
                {
                    dsExtractDataToSave.Tables["Table8"].ImportRow(dr);
                }
                foreach (DataRow dr in objDsFormDetails.Tables["lnk_fieldsConditionalBusinessRule"].Rows)
                {
                    dsExtractDataToSave.Tables["Table9"].ImportRow(dr);
                }

            }
            return dsExtractDataToSave;
        }

        private void btnExport_Click(object sender, EventArgs e)
        {

            DateTime frmDate = Convert.ToDateTime(dtFrmDate.Text.ToString());
            DateTime ToDate = Convert.ToDateTime(dtToDate.Text.ToString()).AddDays(1);
            string location = cmbExpLocation.SelectedValue.ToString();
            if (location == "0")
            {
                //IQCareWindowMsgBox.ShowWindow("BlankSatellite", this);
                //return ;
                IQCareWindowMsgBox.ShowWindow("BlankLocation", this);
                return;
            }

            this.Cursor = Cursors.WaitCursor;
            IImportExportForms theDataDump = (IImportExportForms)ObjectFactory.CreateInstance("BusinessProcess.FormBuilder.BImportExportForms, BusinessProcess.FormBuilder");
            //theDataDump.GetExportDataDump();
            theDataDump.GetExportDataDump(frmDate, ToDate, location);


            DataView dv = new DataView(theDT);
            dv.RowFilter = "FacilityId='" + location + "'";
            string facilityName = string.Empty;
            if (dv.Count > 0)
            {
                facilityName = dv[0].Row["fecname"].ToString().Trim().Replace(' ', '_');
            }


            MessageBox.Show("Export Done. New file IQCareDataDump_" + facilityName + ".xml created in C drive");
            this.Cursor = Cursors.Default;
            ////FileDialog oDialog = new SaveFileDialog();
            ////oDialog.DefaultExt = "xml";
            ////oDialog.FileName = "Export Data-" + DateTime.Today.ToString("ddMMMyyyy") + ".xml";
            ////oDialog.Filter = "Form (*.xml)|*.xml";
            ////if (oDialog.ShowDialog() == DialogResult.OK)
            ////{
            ////    string strFilename = oDialog.FileName;
            ////    //dsExportDataToSave.WriteXml(strFilename);
            ////    theDS.WriteXml(strFilename, XmlWriteMode.WriteSchema);
            ////}
            ////////IImportExportForms objFormDetail;
            ////////DataSet objDsFormNames = new DataSet();
            ////////DataSet dsExportDataToSave = new DataSet();
            ////////DataSet objDsFormDetails = new DataSet();
            ////////objFormDetail = (IImportExportForms)ObjectFactory.CreateInstance("BusinessProcess.FormBuilder.BImportExportForms,BusinessProcess.FormBuilder");
            ////////objDsFormNames = objFormDetail.GetAllFormDetail("1", "0", Convert.ToInt16(GblIQCare.AppCountryId), "");


            ////////for (int i = 0; i < objDsFormNames.Tables[0].Rows.Count; i++)
            ////////{
            ////////    objDsFormDetails = objFormDetail.GetImportExportFormDetail(objDsFormNames.Tables[0].Rows[i]["FormName"].ToString());
            ////////    if (dsExportDataToSave.Tables.Count == 0)
            ////////    {
            ////////        ////mst_feature
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[0].Copy());
            ////////        dsExportDataToSave.Tables[0].TableName = "mst_feature";
            ////////        ////mst_section
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[1].Copy());
            ////////        dsExportDataToSave.Tables[1].TableName = "mst_section";
            ////////        ////lnk_forms
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[2].Copy());
            ////////        dsExportDataToSave.Tables[2].TableName = "lnk_forms";
            ////////        ////Select List Val only for custom fields
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[3].Copy());
            ////////        dsExportDataToSave.Tables[3].TableName = "mst_customFormField";
            ////////        ////Business Rule only for custom fields
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[4].Copy());
            ////////        dsExportDataToSave.Tables[4].TableName = "lnk_fieldsBusinessRule";
            ////////        ////mst_module-tech area
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[5].Copy());
            ////////        dsExportDataToSave.Tables[5].TableName = "mst_patientIdentifier";
            ////////        ////module identifier
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[6].Copy());
            ////////        dsExportDataToSave.Tables[6].TableName = "lnk_patientModuleIdentifier";
            ////////        ////Conditional Fields
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[7].Copy());
            ////////        dsExportDataToSave.Tables[7].TableName = "Vw_FieldConditionalField";
            ////////        ////Select List Val only for Conditional custom fields
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[8].Copy());
            ////////        dsExportDataToSave.Tables[8].TableName = "VW_MasterTableLinking";
            ////////        ////Business Rule only for Conditional custom fields
            ////////        dsExportDataToSave.Tables.Add(objDsFormDetails.Tables[9].Copy());
            ////////        dsExportDataToSave.Tables[9].TableName = "lnk_fieldsConditionalBusinessRule";

            ////////    }
            ////////    else
            ////////    {
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[0].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[0].ImportRow(dr);

            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[1].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[1].ImportRow(dr);
            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[2].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[2].ImportRow(dr);
            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[3].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[3].ImportRow(dr);
            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[4].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[4].ImportRow(dr);
            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[5].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[5].ImportRow(dr);
            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[6].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[6].ImportRow(dr);
            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[7].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[7].ImportRow(dr);
            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[8].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[8].ImportRow(dr);
            ////////        }
            ////////        foreach (DataRow dr in objDsFormDetails.Tables[9].Rows)
            ////////        {
            ////////            dsExportDataToSave.Tables[9].ImportRow(dr);
            ////////        }

            ////////    }
            ////////}
            ////////DataSet theDS;
            ////////IFacility FacilityManager = (IFacility)ObjectFactory.CreateInstance("BusinessProcess.Security.BFacility,BusinessProcess.Security");
            ////////DataSet theFacDS = FacilityManager.GetFacilityData(Convert.ToDateTime(txtDateFrom.Text), Convert.ToDateTime(txtDateTo.Text), Convert.ToInt32(cmbExpLocation.SelectedValue));
            //////////DataTable theDT = theFacDS.Tables[0];
            ////////if (theFacDS.Tables[0].Rows.Count > 0)
            ////////{
            ////////    theDS = GetFacilityData(theFacDS);

            ////////     dsExportDataToSave.Merge(theDS, true, MissingSchemaAction.AddWithKey);
            ////////    FileDialog oDialog = new SaveFileDialog();
            ////////    oDialog.DefaultExt = "xml";
            ////////    oDialog.FileName = "Export Data-" + DateTime.Today.ToString("ddMMMyyyy") + ".xml";
            ////////    oDialog.Filter = "Form (*.xml)|*.xml";
            ////////    if (oDialog.ShowDialog() == DialogResult.OK)
            ////////    {
            ////////        string strFilename = oDialog.FileName;
            ////////        dsExportDataToSave.WriteXml(strFilename);
            ////////    }

            ////////}
            ////////else
            ////////{
            ////////    IQCareWindowMsgBox.ShowWindow("SelectForm", this);
            ////////    txtFileName.Focus();
            ////////}

        }

        //public DataSet GetFacilityData(DataSet theDT)
        //{
        //    IFacility FacilityManager = (IFacility)ObjectFactory.CreateInstance("BusinessProcess.Security.BFacility,BusinessProcess.Security");
        //    DataTable distVisitIddt = new DataTable();
        //    StringBuilder theStr = new StringBuilder();
        //    StringBuilder theStrParam = new StringBuilder();
        //    StringBuilder theStrPtn_pk = new StringBuilder();
        //    StringBuilder theStrCare_Id = new StringBuilder();
        //    string XmlFile = GblIQCare.GetXMLPath() + "Export.xml";
        //    DataSet theDSGetXML = new DataSet();
        //    theDSGetXML.ReadXml(XmlFile);

        //    DataSet theCustomDS = FacilityManager.GetExportData("SELECT name[TableName] FROM sys.Tables"
        //                                                        +" where name Not Like 'DTL_FBCUSTOMFIELD_CareEnd_%' and name like 'DTL_FBCUSTOMFIELD_%';\r\n"
        //                                                        + "SELECT name[TableName] FROM sys.Tables  where name like 'DTL_FBCUSTOMFIELD_CareEnd_%' and name NOT like 'DTL_FBCUSTOMFIELD_CareEnd_HIVCARE-STATICFORM%' ;\r\n"
        //                                                        + "SELECT COLUMN_NAME[ColumnName],TABLE_NAME[TableName] from information_schema.columns c inner Join sys.Tables t on t.name=c.TABLE_NAME" 
        //                                                        +" WHERE c.TABLE_NAME like 'dtl_FB_%' and c.TABLE_NAME NOT like 'DTL_FBCUSTOMFIELD_%' and c.COLUMN_NAME in('Visit_Pk','CareEndedId');");

        //    distVisitIddt = theDT.Tables[0].DefaultView.ToTable(true, "Visit_Id");
        //    foreach (DataRow theDR in distVisitIddt.Rows)
        //    {
        //        theStrParam = theStrParam.Append(theDR["Visit_Id"] + ",");
        //    }
        //    DataTable distinctdt = theDT.Tables[0].DefaultView.ToTable(true, "Ptn_Pk");
        //    foreach (DataRow thePtnDR in distinctdt.Rows)
        //    {
        //        theStrPtn_pk = theStrPtn_pk.Append(thePtnDR["Ptn_pk"] + ",");
        //    }
        //    if (theDT.Tables.Count > 0)
        //    {
        //        if (theDT.Tables.Count>1 && theDT.Tables[1].Rows.Count > 0)
        //        {

        //            DataTable disCaredt = theDT.Tables[1].DefaultView.ToTable(true, "CareEndedID");

        //            foreach (DataRow disCaredr in disCaredt.Rows)
        //            {
        //                theStrCare_Id = theStrCare_Id.Append(disCaredr["CareEndedID"] + ",");
        //            }
        //            theStrCare_Id.Remove(theStrCare_Id.Length - 1, 1);
        //        }
        //    }

        //    theStrParam.Remove(theStrParam.Length - 1, 1);
        //    theStrPtn_pk.Remove(theStrPtn_pk.Length - 1, 1);

        //    theStr = theStr.Append("Declare @SymKey varchar(400);\r\n" +
        //                            "Set @SymKey = 'Open symmetric key Key_CTC decryption by password=''ttwbvXWpqb5WOLfLrBgisw==''';\r\n" +
        //                            "EXEC(@SymKey);\r\n");
        //    foreach (DataRow theDR1 in theDSGetXML.Tables[0].Rows)
        //    {
        //        if (theDR1["TableName"].ToString() == "Mst_patient")
        //        {

        //            theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "],* from " + theDR1["TableName"] + " where ((createdate between '" + Convert.ToDateTime(txtDateFrom.Text) + "' and DATEADD(day,1,'" + Convert.ToDateTime(txtDateTo.Text) + "')) or (updatedate between '" + Convert.ToDateTime(txtDateFrom.Text)
        //                            + "' and DATEADD(day,1,'" + Convert.ToDateTime(txtDateTo.Text) + "'))) and locationid = '" + Convert.ToInt32(cmbExpLocation.SelectedValue) + "' Order by Ptn_Pk;\r\n ");

        //        }
        //        else if (theDR1["TableName"].ToString() == "AppAdmin")
        //        {
        //            theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "], * from " + theDR1["TableName"] + ";\r\n ");

        //        }
        //        else if (theDR1["TableName"].ToString() == "Mst_Module")
        //        {
        //            theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "], * from " + theDR1["TableName"] + ";\r\n ");

        //        }
        //        else if (theDR1["TableName"].ToString() == "dtl_PatientLabResults")
        //        {
        //            theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "], * from " + theDR1["TableName"] + ";\r\n ");

        //        }

        //        else if (theDR1["TableName"].ToString() == "Mst_Visittype")
        //        {
        //            theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "], * from " + theDR1["TableName"] + " where VisitTypeId>100;\r\n ");

        //        }
        //        if (theDR1["VisitIdName"].ToString() != "")
        //        {
        //            if (theDR1["TableName"].ToString() == "dtl_PatientContacts")
        //            {
        //                theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "],ptn_pk,LocationID,EmployeeID,convert(varchar(50), decryptbykey(GuardianName))[GuardianName],convert(varchar(50), decryptbykey(GuardianInformation))[GuardianInformation]" +
        //                                      ",convert(varchar(50), decryptbykey(EmergContactName))[EmergContactName],EmergContactRelation,convert(varchar(50), decryptbykey(EmergContactPhone))[EmergContactPhone]" +
        //                                      ",convert(varchar(50), decryptbykey(EmergContactAddress))[EmergContactAddress],EmergContactKnowsHIVStatus,DiscussStatus,UserID,CreateDate,UpdateDate,TreatmentSupportPhone" +
        //                                      ",TreatmentSupportName,CommunitySupportGroup,TreatmentSupportAddress" +
        //                                      ",VisitId,District,SubCounty,Parish,LC1,Willpickupmedications,Homebasedcareprovidedby,TreatmentSupporterName,TreatmentSupporterContact" +
        //                                      ",ScheduledAppointment,AlternatePickUpdate,Clinicalteamleader FROM " + theDR1["TableName"] + " where " + theDR1["VisitIdName"] + " IN(" + theStrParam + ");\r\n ");

        //            }
        //            else if (theDR1["TableName"].ToString() != "Mst_Module")
        //            {
        //                theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "], * from " + theDR1["TableName"] + " where " + theDR1["VisitIdName"] + " IN(" + theStrParam + ");\r\n ");
        //            }
        //        }
        //        else if (theDR1["VisitIdName"].ToString() == "" && theDR1["PatientID"].ToString() != "" && theDR1["TableName"].ToString() != "Mst_Module" && theDR1["TableName"].ToString() != "dtl_PatientLabResults")
        //        {
        //            theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "], * from " + theDR1["TableName"] + " where " + theDR1["PatientID"] + " IN(" + theStrPtn_pk + ");\r\n ");

        //        }
        //        else if (theDR1["VisitIdName"].ToString() == "" && theDR1["PatientID"].ToString() == "" && theDR1["ID"].ToString() != "")
        //        {
        //            theStr = theStr.Append("Select ''[" + theDR1["TableName"] + "], * from " + theDR1["TableName"] + ";\r\n ");

        //        }


        //    }
        //    if (theCustomDS.Tables.Count > 0)
        //    {
        //        if (theCustomDS.Tables[0].Rows.Count > 0)
        //        {
        //            foreach (DataRow theCustomDR in theCustomDS.Tables[0].Rows)
        //            {
        //                theStr = theStr.Append("Select ''[" + theCustomDR["TableName"] + "], * from " + theCustomDR["TableName"] + " where Visit_Pk IN(" + theStrParam + ");\r\n ");

        //            }
        //        }
        //        if (theDT.Tables.Count > 1 && theCustomDS.Tables[1].Rows.Count > 0)
        //        {
        //            foreach (DataRow theCustomDR in theCustomDS.Tables[1].Rows)
        //            {
        //                theStr = theStr.Append("Select ''[" + theCustomDR["TableName"] + "], * from " + theCustomDR["TableName"] + " where CareEndedId IN(" + theStrCare_Id + ");\r\n ");

        //            }
        //        }
        //        if (theDT.Tables.Count > 2 && theCustomDS.Tables[2].Rows.Count > 0)
        //        {
        //            foreach (DataRow theCustomDR in theCustomDS.Tables[2].Rows)
        //            {
        //                if (theCustomDR["ColumnName"].ToString() == "CareEndedId")
        //                {
        //                    theStr = theStr.Append("Select ''[" + theCustomDR["TableName"] + "], * from " + theCustomDR["TableName"] + " where " + theCustomDR["ColumnName"] + " IN(" + theStrCare_Id + ");\r\n ");
        //                }
        //                else if (theCustomDR["ColumnName"].ToString() == "Visit_Pk")
        //                    theStr = theStr.Append("Select ''[" + theCustomDR["TableName"] + "], * from " + theCustomDR["TableName"] + " where " + theCustomDR["ColumnName"] + " IN(" + theStrParam + ");\r\n ");

        //            }
        //        }
        //    }
        //    theStr = theStr.Append("DECLARE @FieldName varchar(max);\r\n"
        //                           + "SELECT @FieldName=SUBSTRING((SELECT ',' + Convert(Varchar,FieldName) FROM mst_patientidentifier"
        //                            + " FOR XML PATH('')),2,200000);\r\n"
        //                            + "declare @str varchar(max);\r\n"
        //                            + "set @str ='select ''''[PatientIdentifierValue],ptn_pk,' + @FieldName + ' from Mst_Patient ';\r\n"
        //                            + "exec(@str);\r\n");

        //    theStr = theStr.Append("Close symmetric key Key_CTC");
        //    DataSet theDS = FacilityManager.GetExportData(theStr.ToString());
        //    return theDS;
        //}

        private int ImportForms(DataSet theImportFormsDS)
        {
            int iRes = 0;

            IImportExportForms objImportFormDetails;
            objImportFormDetails = (IImportExportForms)ObjectFactory.CreateInstance("BusinessProcess.FormBuilder.BImportExportForms,BusinessProcess.FormBuilder");
            if (theImportFormsDS.Tables.Count > 0)
            {
                iRes = objImportFormDetails.ImportForms(theImportFormsDS, GblIQCare.AppUserId, System.Convert.ToInt32(GblIQCare.AppCountryId));
            }

            return iRes;
        }

        public void CreateTables()
        {
            try
            {
                string theTSQL = "";
                foreach (DataTable theDT in theTableDS.Tables)
                {
                    theTSQL = "";
                    foreach (DataColumn theCol in theDT.Columns)
                    {
                        if (theTSQL == "")
                            theTSQL = "[" + theCol.ColumnName + "] " + theCol.DataType.ToString().Replace("System.Int32", "int").Replace("System.String", "Varchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS").Replace("System.DateTime", "DateTime").Replace("System.Boolean", "bit").Replace("System.Int16", "int").Replace("System.Byte[]", "Varbinary(max)").Replace("System.Decimal", "Decimal(18,2)").Replace("System.Guid", "int").Replace("System.Int64", "bigint");
                        else
                            theTSQL = theTSQL + ",[" + theCol.ColumnName + "] " + theCol.DataType.ToString().Replace("System.Int32", "int").Replace("System.String", "Varchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS").Replace("System.DateTime", "DateTime").Replace("System.Boolean", "bit").Replace("System.Int16", "int").Replace("System.Byte[]", "Varbinary(max)").Replace("System.Decimal", "Decimal(18,2)").Replace("System.Guid", "int").Replace("System.Int64", "bigint");
                    }
                    theTSQL = "Use [IQCare_Tmp]; Create Table [" + theDT.TableName + "] (" + theTSQL + ")";
                    IImportExportForms theDataImport = (IImportExportForms)ObjectFactory.CreateInstance("BusinessProcess.FormBuilder.BImportExportForms,BusinessProcess.FormBuilder");
                    DataSet theTmpDS = theDataImport.ReturnQueryResult(theTSQL);
                    theDataImport.InsertBulk(theDT);
                    theTmpDS.Dispose();
                    theTmpDS = null;

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private void btnImport_Click(object sender, EventArgs e)
        {
            try
            {
                if (txtFileName.Text.ToString() != "")
                {

                    this.Cursor = Cursors.WaitCursor;
                    DataSet theVerDS = new DataSet();
                    DataTable theVerDT = new DataTable();
                    theVerDS.ReadXml(txtFileName.Text.ToString());
                    theVerDT = theVerDS.Tables["AppAdmin"];
                    if (theVerDT.Rows[0]["AppVer"].ToString() != GblIQCare.AppVersion && String.Format("{0:dd-MMM-yyyy}", Convert.ToDateTime(theVerDT.Rows[0]["RelDate"])) != GblIQCare.ReleaseDate.ToString())
                    {
                        IQCareWindowMsgBox.ShowWindow("CheckVersion", this);
                        this.Cursor = Cursors.Default;
                        return;
                    }


                    if (cmbImpLocation.SelectedIndex > 0)
                    {
                        DataTable theFacilityDT = new DataTable();
                        theFacilityDT = theVerDS.Tables["mst_facility"];
                        string facilityName = theFacilityDT.Rows[0]["FacilityName"].ToString();
                        DataView dv = new DataView(theDT);
                        dv.RowFilter = "FecName='" + facilityName + "'";

                        if (dv.Count > 0)
                        {
                            // ok 
                        }
                        else
                        {
                            IQCareWindowMsgBox.ShowWindow("CheckImportFacility", this);
                            this.Cursor = Cursors.Default;
                            return;
                        }
                    }



                    theTableDS.ReadXml(txtFileName.Text.ToString(), XmlReadMode.ReadSchema);
                    StringBuilder theTSQL = new StringBuilder();
                    theTSQL.Append("IF EXISTS(SELECT name FROM sys.databases WHERE name = 'IQCare_Tmp')\r\n Begin \r\n DROP DATABASE IQCare_Tmp;  \r\n End \r\n Create Database IQCare_Tmp; \r\n");
                    IImportExportForms theDataImport = (IImportExportForms)ObjectFactory.CreateInstance("BusinessProcess.FormBuilder.BImportExportForms,BusinessProcess.FormBuilder");
                    DataSet theTmpDS = theDataImport.ReturnQueryResult(theTSQL.ToString());
                    theTSQL = new StringBuilder();
                    theTSQL.Append("declare @tsql varchar(5000)\r\n set @tsql = 'Use [IQCare_Tmp] ; IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[fngetColumn]'') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT''))\r\n BEGIN \r\n");
                    theTSQL.Append("execute dbo.sp_executesql @statement = N''CREATE FUNCTION dbo.fngetColumn \r\n( \r\n @TABLENAME varchar(100),@Identcol varchar(50) \r\n )\r\n RETURNS VARCHAR(max) AS \r\n BEGIN \r\n DECLARE @ColumnsNames varchar(max)\r\n");
                    theTSQL.Append("if(@Identcol='''''''' or @Identcol IS NULL)\r\n Begin \r\n SELECT @ColumnsNames =  COALESCE(@ColumnsNames + '''','''', '''''''')+ CAST(COLUMN_NAME AS varchar(50)) FROM INFORMATION_SCHEMA.Columns where TABLE_NAME =@TABLENAME \r\n");
                    theTSQL.Append("End \r\n Else \r\n Begin \r\n SELECT @ColumnsNames =  COALESCE(@ColumnsNames + '''','''', '''''''')+ CAST(COLUMN_NAME AS varchar(50)) FROM INFORMATION_SCHEMA.Columns where TABLE_NAME =@TABLENAME AND COLUMN_NAME != @Identcol \r\n  End \r\n return @ColumnsNames \r\n END \r\n ''END' exec (@tsql)");
                    theDataImport = (IImportExportForms)ObjectFactory.CreateInstance("BusinessProcess.FormBuilder.BImportExportForms,BusinessProcess.FormBuilder");
                    theTmpDS = theDataImport.ReturnQueryResult(theTSQL.ToString());

                    theTSQL = new StringBuilder();
                    theTSQL.Append("declare @tsql varchar(5000)\r\n set @tsql = 'Use [IQCare_Tmp] ; IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[fngetUpdateColumn]'') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT''))\r\n BEGIN \r\n");
                    theTSQL.Append("execute dbo.sp_executesql @statement = N'' CREATE FUNCTION dbo.fngetUpdateColumn \r\n(\r\n @TABLENAME varchar(100),@Identcol varchar(50) \r\n )\r\n RETURNS VARCHAR(max) AS \r\n BEGIN \r\n DECLARE @UpdateColName  varchar(max)\r\n");
                    theTSQL.Append("if(@Identcol='''''''' or @Identcol IS NULL)\r\n Begin \r\n SELECT @UpdateColName =  COALESCE(@UpdateColName + '''','''', '''''''')+''''[''''+  CAST(COLUMN_NAME AS varchar(50)) +'''']=b.[''''+ CAST(COLUMN_NAME AS varchar(50))+'''']''''");
                    theTSQL.Append(" FROM INFORMATION_SCHEMA.Columns where TABLE_NAME =@TABLENAME \r\n End \r\n Else \r\n Begin \r\n  SELECT @UpdateColName =  COALESCE(@UpdateColName + '''','''', '''''''')+''''[''''+ CAST(COLUMN_NAME AS varchar(50)) +'''']=b.[''''+ CAST(COLUMN_NAME AS varchar(50))+'''']'''' FROM INFORMATION_SCHEMA.Columns");
                    theTSQL.Append(" where TABLE_NAME =@TableName AND COLUMN_NAME != @Identcol  \r\n  End \r\n SET @UpdateColName=SUBSTRING(@UpdateColName,1,LEN(@UpdateColName)) \r\n return @UpdateColName \r\n END \r\n ''END' exec (@tsql)");


                    theDataImport = (IImportExportForms)ObjectFactory.CreateInstance("BusinessProcess.FormBuilder.BImportExportForms,BusinessProcess.FormBuilder");
                    theTmpDS = theDataImport.ReturnQueryResult(theTSQL.ToString());

                    theTSQL = new StringBuilder();
                    theTSQL.Append("declare @tsql varchar(5000)\r\n set @tsql = 'Use [IQCare_Tmp] ; IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[fn_getImportDataTable]'') AND type in (N''FN'', N''IF'', N''TF'', N''FS'', N''FT''))\r\n BEGIN \r\n");
                    theTSQL.Append("execute dbo.sp_executesql @statement = N''CREATE FUNCTION dbo.fn_getImportDataTable(@param1 varchar(100),@param2 varchar(100),@param3 varchar(100),@param4 varchar(100))\r\n");
                    theTSQL.Append("RETURNS @tblsNames  table (tblname varchar(500) )\r\n AS \r\n BEGIN \r\n INSERT @tblsNames Select name as tblname from sysobjects where name  like ''''dtl%'''' and name not like @param1");
                    theTSQL.Append(" and name not like @param2 and name not like @param3 and name not like @param4 and type = ''''u'''' \r\n return END \r\n ''END' exec (@tsql)");
                    theDataImport = (IImportExportForms)ObjectFactory.CreateInstance("BusinessProcess.FormBuilder.BImportExportForms,BusinessProcess.FormBuilder");
                    theTmpDS = theDataImport.ReturnQueryResult(theTSQL.ToString());
                    theTSQL.Remove(0, theTSQL.Length);
                    //Thread.SpinWait(10000);
                    System.Threading.Thread.CurrentThread.Join(10);
                    CreateTables();

                    int import = theDataImport.ImportDatabase();

                    MessageBox.Show("Complete");
                    this.Cursor = Cursors.Default;


                }
                else
                {
                    IQCareWindowMsgBox.ShowWindow("BrowseFile", this);
                    txtFileName.Focus();
                }
            }
            catch (Exception err)
            {
                MsgBuilder theBuilder = new MsgBuilder();
                theBuilder.DataElements["MessageText"] = err.Message.ToString();
                IQCareWindowMsgBox.ShowWindowConfirm("#C1", theBuilder, this);
            }
        }


    }
}
